package cn.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.pojo.Role;
import cn.util.DBUtil;

public class RoleMappleImp extends DBUtil implements RoleMapper {

	@Override
	public Role getRoleById(Integer userRole) {
		String sql = "select * from smbms_role  where id =?;";
		List<Object> list = new ArrayList<>();
		list.add(userRole);
		DBUtil db = new DBUtil();
		List<Role> role = db.getT(Role.class, sql, list);
		return role.get(0);
	}

	@Override
	public List<Role> getAllRoles() {
		String sql = "select * from smbms_role;";
		List<Object> list = new ArrayList<>();
		DBUtil db = new DBUtil();
		List<Role> role = db.getT(Role.class, sql, list);
		return role;
	}

	@Override
	public int addRole(Role role) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = "INSERT INTO `smbms_role`(`roleCode`, `roleName`,"
				+ " `createdBy`, `creationDate`) VALUES (?,?,?,?);";
		try {
			ps = ct.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setObject(1, role.getRoleCode());
			ps.setObject(2, role.getRoleName());
			ps.setObject(3, role.getCreatedBy());
			ps.setObject(4, role.getCreationDate());
			int i = ps.executeUpdate();
			st = ps.getGeneratedKeys();
			ct.commit();
			while (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			try {
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			release(ct, ps, null);
		}
		return 0;
	}

	@Override
	public int totalCount(String roleName) {
		String sql = " select count(id) from `smbms_role` where 1=1  ";
		List<Object> list = new ArrayList<Object>();
		if (roleName != null && !roleName.equals("")) {
			sql += " and roleName like ? ;";
			list.add("%" + roleName + "%");
		}
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return 0;
	}

	@Override
	public List<Role> getRoleLike(String roleName, Integer rowNum, int pagecount) {
		String sql = " select * from `smbms_role` where 1=1  ";
		List<Object> list = new ArrayList<Object>();
		if (roleName != null && !roleName.equals("")) {
			sql += " and roleName like ? ";
			list.add("%" + roleName + "%");
		}
		sql += "  limit ?,? ;";
		list.add(rowNum);
		list.add(pagecount);
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		System.out.println(sql);
		List<Role> roleList = new ArrayList<Role>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				Role role = new Role();
				role.setCreatedBy(st.getLong("createdBy"));
				role.setCreationDate(st.getTimestamp("creationDate"));
				role.setId(st.getLong("id"));
				role.setModifyBy(st.getLong("modifyBy"));
				role.setModifyDate(st.getTimestamp("modifyDate"));
				role.setRoleCode(st.getString("roleCode"));
				role.setRoleName(st.getString("roleName"));
				roleList.add(role);
			}
			return roleList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return roleList;
	}

	@Override
	public Role getRoleByRoleCode(String roleCode) {
		String sql = "select * from `smbms_role` where rolecode=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setString(1, roleCode);
			st = ps.executeQuery();
			Role role = null;
			while (st.next()) {
				role = new Role();
				role.setCreatedBy(st.getLong("createdBy"));
				role.setCreationDate(st.getTimestamp("creationDate"));
				role.setId(st.getLong("id"));
				role.setModifyBy(st.getLong("modifyBy"));
				role.setModifyDate(st.getTimestamp("modifyDate"));
				role.setRoleCode(st.getString("roleCode"));
				role.setRoleName(st.getString("roleName"));
			}
			return role;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public Role getRoleByRoleName(String roleName) {
		String sql = "select * from `smbms_role` where rolename=? ;";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			ps.setString(1, roleName);
			st = ps.executeQuery();
			Role role = null;
			while (st.next()) {
				role = new Role();
				role.setCreatedBy(st.getLong("createdBy"));
				role.setCreationDate(st.getTimestamp("creationDate"));
				role.setId(st.getLong("id"));
				role.setModifyBy(st.getLong("modifyBy"));
				role.setModifyDate(st.getTimestamp("modifyDate"));
				role.setRoleCode(st.getString("roleCode"));
				role.setRoleName(st.getString("roleName"));
			}
			return role;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public int roleRemove(String id) {
		String sql = " delete from smbms_role where id =?  ";
		Integer[] num = cud(Role.class, sql, id);
		if (num.length > 0) {
			return num[0];
		}
		return 0;
	}

	@Override
	public Integer[] deleteAll(String[] id) {
		String sql = " delete from smbms_role where id in ( ";
		for (int i = 0; i < id.length; i++) {
			if (i < id.length - 1) {
				sql += id[i] + ",";
			} else {
				sql += id[i];
			}
		}
		sql += " );";
		Integer[] num = cud(Role.class, sql);
		return num;
	}

}
